1. Technical Field
The present invention relates in general to data processing systems, and more particularly to data processing systems including a database management system. Still more particularly, the present invention provides apparatus and methods in a database management system including a pre-load cursor for returning primary keys for rows meeting search criteria to improve database searching efficiency.
2. Description of Related Art
Nearly all high-level programming languages support various constructs, such as constants, variables, composite types, collection types, etc. for manipulating data. For some applications, particularly data management systems, these constructs have proved to be insufficient. The DBMS manages all requests for database action, such as queries or updates, from the user. In addition, a DBMS permits centralized control of security and data integrity requirements.
Many data processing systems take advantage of distributed processing by using a client/server architecture. In this architecture, the database is divided into two parts: a front-end or a client portion, and a back-end or a server portion. The client portion concentrates on requesting, processing, and presenting data managed by the server portion. The server portion runs server software and handles the functions required for concurrent, shared data access. Of course, the client-server architecture can be distributed across a network. For example, each of the client applications may be executed at a different node of the network, while one or more other nodes on the network are used for storing the database and executing the server software to process database requests sent by users at the client nodes.
To request data from a database, a client application may have a query written in any number of programming languages. One such language is the industry standard Structured Query Language (SQL) defined by the International Standards Organization (ISO). In response to executing the client application, the client will cause the server portion to perform the required operations on the information in the database.
As a result of the insufficiencies of general-purpose high level programming data constructs, special-purpose constructs referred to herein as xe2x80x9ccursorsxe2x80x9d are supported by various database languages. A cursor is a handle, i.e. a name or pointer, to the set of rows produced in response to executing a specific query statement. The term xe2x80x9ccursorxe2x80x9d is a generic term which may mean one of several different types of pointers.
Two different prior art server implementations are described below. Each server implementation describes a different cursor method for locating and returning data to a requesting client.
The first implementation is a stateless application server and is illustrated by the block diagram of FIG. 1. In this implementation, a client is coupled to an application server which is coupled to a database. The client first initiates a session with the server and transmits query criteria to the application server requesting a particular number of data rows which meet the query criteria. When the application server receives the query criteria, the application server will create a cursor and initiate a session with the database. The cursor then queries the database to obtain a result set which includes copies of all of the rows which meet the search criteria even though only a smaller number was requested. The application server""s session with the database is ended after copies of all of the rows are retrieved. The cursor in the application server then returns the copies of all of the rows to the client. After the client receives copies of all of the rows, it ends its session with the server.
The client may then initiate another session with the server and transmit a query for a second number of data rows meeting the same criteria. In this case, the process described above is repeated. The server initiates a new session with the database, and a new cursor is created which then searches the database again for the same rows. All of the rows are again retrieved from the database and returned to the client.
This implementation suffers from several disadvantages. The client requested only a particular number of rows, but the cursor retrieved and returned all rows meeting the criteria. Returning all rows takes a longer time and more bandwidth than returning only a limited number of rows. Further, the server may need to search the database multiple times for the same data.
The second implementation is a stateful application server and is illustrated by the block diagram of FIG. 2. In this implementation, a client is also coupled to an application server which is coupled to a database. The client again initiates a session with the server and transmits query criteria to the application server requesting a particular number of data rows which meet the query criteria. When the application server receives the query criteria, a cursor is created and a session between the server and the database is initiated. The cursor queries the database to obtain a result set which includes copies of all of the rows which meet the search criteria. The cursor in the application server then returns only copies of the requested number of data rows from the result set to the client. The client may subsequently request a second number of copies of these same data rows. In this case, the cursor will return copies of the requested number of data rows using the result set obtained from its first search of the database without having to search the database again.
The application server""s session with the database remains open until the client closes its session with the server. Therefore, if the client continues to transmit queries for a number of the rows meeting the same criteria, the session the application server initiated with the database remains open.
This implementation suffers from several disadvantages. The application server has exclusive use of a database connection until the client closes its session with the application server. This ties up the database resource unnecessarily. Once the server obtained its result set, it did not need to access the database further to respond to subsequent queries for data meeting the criteria specified in the first query. Other clients are prohibited from using that database connection while the database is tied up with this client even though the application server has already received the results it requested.
Therefore, a need exists for a method and system including a database for a pre-load cursor whereby database connections are available in between client requests for data rows, and whereby only the number of requested rows are returned.
A method and system are described in a data processing system including a database for efficiently retrieving copies of data rows from the database. Query criteria are received by a server from a client which specifies search criteria describing data rows and which requests a particular number of the data rows to return. Each of the data rows is associated with a unique primary key which uniquely identifies a particular data row. The primary key for each of the data rows meeting the specified criteria is first retrieved from the database by the server. Thereafter, copies of only the particular number of the data rows are retrieved from the database using the primary keys. Copies of only the particular number of data rows are then returned to the requesting client.
The above as well as additional objectives, features, and advantages of the present invention will become apparent in the following detailed written description.